package reservation;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


import logic.ConnectPRO;

public class DB_Reservation {
        private ResultSet result = null;
        private PreparedStatement prepar = null;
        private Statement state = null;
        private int price;
        private int id_room;
        
        
        //metoda wyszukujaca cene za pokoj wg podanych kryterii
        protected void select_db(String osoby, String stand, String local, HttpServletRequest request, HttpServletResponse response ) throws SQLException, IOException{
                ConnectPRO.connectpro();
                state = ConnectPRO.connect.createStatement();           
                result = state.executeQuery("select distinct price, id_room from ROOM natural join HOTEL where free = 'YES' and places = '" + osoby + "' and standard = '" + stand + "' and location = '" + local + "'");
                writeResult(result, request, response);
                
        }
        
        //metoda wyisujaca wyniki w konsoli
        private void writeResult(ResultSet result, HttpServletRequest request, HttpServletResponse response) throws IOException{
                try {
                        while(result.next()){
                                price = result.getInt("price");
                                id_room = result.getInt("id_room");                                                             
                        }
                } catch (SQLException e) {
                        e.printStackTrace();
                }
        }
        
        //metoda zapisujaca rezerwacje do bazy
        public void addToDB(int id_client, int id_room, int cena, java.sql.Date data1, java.sql.Date data2) {           
                
                DateFormat currentDate = new SimpleDateFormat("yyyy-MM-dd");
                Date today = Calendar.getInstance().getTime();
                String data3 = currentDate.format(today);
                java.sql.Date date3 = Reservation.toSqlDate(data3);     
                
                ConnectPRO.connectpro();                
                PreparedStatement prepar;
                try {
                        prepar = ConnectPRO.connect.prepareStatement("insert into RESERVATION values(default, ?, ?, ?, ?, ?, ?)");
                        prepar.setInt(1, id_client);
                        prepar.setInt(2, id_room);
                        prepar.setInt(3, cena);
                        prepar.setDate(4, data1);
                        prepar.setDate(5, data2);
                        prepar.setDate(6, date3);
                        prepar.executeUpdate();
                        System.out.println("id_cli: " + id_client + ", id_room: " + id_room + ", cena: " + cena + ", data_1: " + data1 + ", data_2: " + data2 + ", data3");
                } catch (SQLException e) {
                        
                        e.printStackTrace();
                }
        
        
        }

        //metoda zmieniajaca pokoj na 'zajety'
        public void updateRoom(int id_room) throws SQLException{
                ConnectPRO.connectpro();
                state = ConnectPRO.connect.createStatement();           
                state.executeUpdate("update ROOM set free = 'NO' where id_room = " + id_room);
        }
        
        public int getPrice() {
                return price;
        }
        
        public int getId_room() {
                return id_room;
        }

}
